﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SQLite;
using System.Data;
using PDAMain.Entity;
using PDAMain.Interface;
using Newtonsoft.Json;

namespace PDAMain.DAL.Dao
{
    class RepairWorkDao : BaseDao
    {
        private string assignmentId = "";
        public string AssignmentId
        {
            get { return assignmentId; }
            set { assignmentId = value; }
        }
        private string assetNum = "";
        public string AssetNum
        {
            get { return assetNum; }
            set { assetNum = value; }
        }
        /// <summary>
        /// 获取待处理的维修工单
        /// </summary>
        /// <returns></returns>
        public DataSet list()
        {
            //尝试下载最新数据
            this.download();

            DataSet repairListDt = new DataSet();
           // string str_Sql = "select WONUM,LOCATION,DESCRIPTION,case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别' end as STATUS from repairWorkList";
            string str_Sql = "select ASSIGNMENTID,WONUM,ASSETNUM,LOCDESCRIPTION,DESCRIPTION,case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '4' then '待料' when '5' then '领料' when '9' then '已处理' else '无法识别' end as STATUS from repairWorkList where STATUS in ('1','5')";
            repairListDt = base.executeDataSet(str_Sql);
            return repairListDt;
        }
        /// <summary>
        /// 获取待处理工单的数量
        /// </summary>
        /// <returns></returns>
        public string dataNum()
        {
            this.download();
            string num = "0";

            string str_Sql = "select count(*) from repairWorkList where STATUS='1' or STATUS='5'";

            num = base.executeDataSet(str_Sql).Tables[0].Rows[0][0].ToString();
            if (Convert.ToInt32(num) > 99) num = "99+";
            return num;

        }
        /// <summary>
        ///简单查询—— 查询符合条件的维修工单
        /// </summary>
        /// <param name="status">状态</param>
        /// <param name="time">时间</param>
        /// <returns>符合条件的维修工单</returns>
        public DataSet select(string status, string time)
        {
            //尝试下载最新数据
            this.download();

            DataSet repairSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string nowDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
            //string nowDate1 = DateTime.Now.AddDays(1).ToString();
            string strResult = pda.queryRepairWorkOrder("", status, "", time, nowDate, Identity.dutyPerson);
            if (strResult == string.Empty)
                return null;
            repairSelDt = base.convertToDataSet(strResult);
            return repairSelDt;
        }

        /// <summary>
        /// 高级查询
        /// </summary>
        /// <param name="repairID">维修工单号</param>
        /// <param name="assetNo">资产编号</param>
        /// <param name="dateStart">起始时间</param>
        /// <param name="dateEnd">结束时间</param>
        /// <returns></returns>
        public DataSet complexSel(string repairID, string assetNo, string dateStart, string dateEnd)
        {
            //尝试下载最新数据
            this.download();

            DataSet complexSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string strResult = pda.queryRepairWorkOrder(repairID, "", assetNo, dateStart, dateEnd, Identity.dutyPerson);
            if (strResult == string.Empty)
                return null;
            complexSelDt = base.convertToDataSet(strResult);
            return complexSelDt;
        }
        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="repairID">维修工单号</param>
        /// <returns></returns>
        public DataSet getDetail(string assignmentId)
        {
            //尝试下载最新数据
            this.download();

            DataSet repairDetailDt = new DataSet();
            string str_Sql = "select * from repairWorkList where ASSIGNMENTID='" + assignmentId + "'";
            repairDetailDt = base.executeDataSet(str_Sql);
            return repairDetailDt;
        }

        /// <summary>
        /// 维修工单处理完成，转单均调用此方法更改工单状态
        /// </summary>
        /// <param name="repairID">维修工单号</param>
        /// <param name="status">状态</param>
        /// <param name="time">处理时间</param>
        /// <param name="reason">原因</param>
        public bool deal(string assignmentId, string status, string time, string remark)
        {
            //此處處理狀態需同時在本地數據庫和Web端更新狀態
            bool result = false;
            if (this.updateRepairStatus(assignmentId, status, remark))
            {
                //已通过WebService更新服务器状态，删除本地数据
                result = base.executeNonQuery("delete from repairWorkList where ASSIGNMENTID='" + assignmentId + "'");
            }/*
            else
            {
                string sql = "update repairWorkList set remark='" + remark + "', "
                                + "status='" + status + "', "
                                + "STATUSDATE='" + time + "', "
                                + "STARTTIME='" + Identity.StartTime + "', ENDTIME='" + Identity.EndTime + "'"
                                + " where ASSIGNMENTID='" + assignmentId + "'";
                return base.executeNonQuery(sql);
            }*/
            return result;
        }

        public bool insertLacolTable(string assignmentId, string status, string time, string remark)
        {
            bool result = false;
            string sql = "update repairWorkList set remark='" + remark + "', "
                                + "status='" + status + "', "
                                + "STATUSDATE='" + time + "', "
                                + "STARTTIME='" + Identity.StartTime + "', ENDTIME='" + Identity.EndTime + "'"
                                + " where ASSIGNMENTID='" + assignmentId + "'";
            result = base.executeNonQuery(sql);
            return result;
        }

        /// <summary>
        /// 同步数据
        /// </summary>
        public void syncData()
        {
            this.download();
            this.upload();
        }
        /// <summary>
        /// 下载数据
        /// </summary>
        public void download()
        {
            DataSet ds = this.getRepairDataSet();
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //驗證系統中是否存在該筆數據
                    string strAssignmentid = dt.Rows[i]["ASSIGNMENTID"].ToString();

                    if (this.verifyData(strAssignmentid))
                    {
                        this.insertData(dt.Rows[i]);
                    }
                }
            }
        }
        /// <summary>
        /// 上传数据
        /// </summary>
        public void upload()
        {
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                //在wifi或gprs打开时都可以上传状态
                //string sqlRepair = "select WONUM,status,remark,STATUSDATE from repairWorkList where status !='1'";
                string sqlRepair = "select WONUM,status,remark,STATUSDATE from repairWorkList where status not in ('1','4')";
                DataSet ds = base.executeDataSet(sqlRepair);
                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    for (int index = 0; index < dt.Rows.Count; index++)
                    {
                        string repairID = dt.Rows[index]["WONUM"].ToString().Trim();
                        string status = dt.Rows[index]["status"].ToString().Trim();
                        string remark = dt.Rows[index]["remark"].ToString().Trim();

                        if (this.updateRepairStatus(repairID, status, remark))
                        {
                            base.executeNonQuery("delete from repairWorkList where WONUM='" + repairID + "'");
                        }
                    }
                }
            }
            if (NetWorkState.checkWifiState())
            {
                //仅在wifi打开时上传图片
            }
        }

        /// <summary>
        /// 同步时判断本地数据库是否相同的记录，有则不同步
        /// </summary>
        /// <param name="activityUID">维修工单编号</param>
        /// <returns></returns>
        private bool verifyData(string assignmentid)
        {
            bool result = false;
            DataSet ds = base.executeDataSet("select 1 from repairWorkList where ASSIGNMENTID='" + assignmentid + "'");
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    result = true;
                return result;
            }
            catch
            {
                return result;
            }
        }
        /// <summary>
        /// 同步數據
        /// </summary>
        /// <param name="dr"></param>
        private bool insertData(DataRow dr)
        {
            bool successFlag = false;
            string str_Sql = "insert into repairWorkList(ASSIGNMENTID,WONUM,DESCRIPTION,LOCATION,LOCDESCRIPTION,ASSETNUM,ASSDESCRIPTION,STATUS,remark,STATUSDATE) values("
                + "'" + dr["ASSIGNMENTID"] + "','" + dr["WONUM"] + "','"
                + dr["DESCRIPTION"] + "','" + dr["LOCATIONID"] + "','"
                + dr["LOCDESCRIPTION"] + "','" + dr["ASSETNO"] + "','"
                + dr["ASSETDESCRIPTION"] + "','" + dr["STATUS"] + "','','')";
            try
            {
                successFlag = base.executeNonQuery(str_Sql);
                return successFlag;
            }
            catch (Exception ex)
            {
                return successFlag;
            }
        }
        
        /// <summary>
        /// 通过webservice同步PDA数据
        /// </summary>
        /// <returns></returns>
        private DataSet getRepairDataSet()
        {
            DataSet ds = new DataSet();
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                //鏈接webService接口
                pdaWebReference.PdaService pda = base.getPdaWebService();
                string result = pda.listRepairWorkOrder(Identity.dutyPerson);
                if (result != "")
                {
                    return base.convertToDataSet(result);
                }
            }
            return ds;
        }
        /// <summary>
        /// 通过WebService更新服务器状态
        /// </summary>
        /// <param name="repairId">工单</param>
        /// <param name="status">状态</param>
        /// <param name="comment">备注</param>
        /// <returns></returns>
        private bool updateRepairStatus(string repairId, string status, string comment)
        {
            if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            {
                try
                {
                    //鏈接webService接口
                    pdaWebReference.PdaService pda = base.getPdaWebService();
                    return pda.updateRepairWorkOrder(repairId, status, comment, Identity.dutyPerson, Identity.StartTime, Identity.EndTime);
                }
                catch (Exception ex)
                {
                    return false;
                }

            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取资产备件信息
        /// </summary>
        /// <param name="barcode">资产编号</param>
        /// <returns></returns>
        public DataTable GetAssetDetail(string barcode)
        {
            DataTable dt = null;
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string result = pda.listAttachment(barcode);
            if (result == string.Empty)
            {
                return null;
            }
            dt = base.convertToDataSet(result).Tables[0];
            return dt;
        }

        public string CheckAssetInfo(string barcode)
        {
            string result = "";
            try
            {
                pdaWebReference.PdaService pda = base.getPdaWebService();
                string strlist = pda.listAttachment(barcode);
                List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
                JsonResult Jsonresult = JsonConvert.DeserializeObject<JsonResult>(strlist);
                if (Jsonresult.message == "没有资产信息" || Jsonresult.message == "没有数据")
                {
                    result = Jsonresult.message;
                }
            }
            catch
            {
                result = "请检查webservice";
            }
            return result;
        }

        /// <summary>
        /// 往资产备件表插入一条数据
        /// </summary>
        /// <param name="p_wonum">维修工单号</param>
        /// <param name="p_barcode">资产条码</param>
        /// <param name="itemnum">物料编号</param>
        /// <param name="quantity">数量</param>
        /// <param name="p_person">提交人</param>
        /// <returns></returns>
        public bool InsertRepairAsset(string p_wonum, string p_barcode, string p_detail, string p_person, string sparepartId)
        {
            bool successFlag = false;
            string sql = "insert into RepairAsswet(WONUM,BARCODE,DETAIL,ACCOUNT,SPAREPARTID,CREATEDT) values('" + p_wonum
                + "','" + p_barcode + "','" + p_detail + "','" + p_person + "','" + sparepartId 
                + "','" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "')";
            try
            {
                successFlag = base.executeNonQuery(sql);
                if (successFlag)
                {
                    sql = "update repairWorkList set status='4',STARTTIME='" + Identity.StartTime + "', ENDTIME='" + Identity.EndTime + "' where ASSIGNMENTID='" + DataHelper.RepairWorkDao.AssignmentId + "'";
                    successFlag = base.executeNonQuery(sql);
                }
            }
            catch { }
            return successFlag;
        }

        public DataTable GetRepairAssetInfo()
        {
            DataTable ds = null;
            string sql = "select r.WONUM,r.BARCODE,r.DETAIL,r.ACCOUNT,r.SPAREPARTID,w.STARTTIME,w.ENDTIME,w.REMARK from RepairAsswet r inner join repairWorkList w on r.SPAREPARTID=w.ASSIGNMENTID order by CREATEDT";
            try
            {
                ds = base.executeDataSet(sql).Tables[0];
            }
            catch { }
            return ds;
        }
        /*
        /// <summary>
        /// 获取本地没上传的资产备件信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetRepairAssetInfo()
        {
            DataTable dt = CreateRepairAssetInfo();
            DataSet ds_Sparepartid = GetSparepartid();
            if (ds_Sparepartid != null)
            {
                DataTable ds = null;
                string sql = "select WONUM,BARCODE,ITEMNUM,QUANTITY,ACCOUNT,SPAREPARTID from RepairAsswet where SPAREPARTID='" + ds_Sparepartid.Tables[0].Rows[0]["SPAREPARTID"].ToString() + "'";
                try
                {
                    ds = base.executeDataSet(sql).Tables[0];
                    string wonum = ds.Rows[0]["WONUM"].ToString().Trim();
                    string barcode = ds.Rows[0]["BARCODE"].ToString().Trim();
                    string person = ds.Rows[0]["ACCOUNT"].ToString().Trim();
                    string sparepartId = ds.Rows[0]["sparepartId"].ToString().Trim();
                    string detail = string.Empty;
                    for (int i = 0; i < ds.Rows.Count; i++)
                    {
                        string itemnum = ds.Rows[0]["ITEMNUM"].ToString().Trim();
                        string quantity = ds.Rows[0]["QUANTITY"].ToString().Trim();
                        if (i == dt.Rows.Count - 1)
                        {
                            detail += itemnum + "," + quantity;
                        }
                        else
                        {
                            detail += itemnum + "," + quantity + ";";
                        }
                    }
                    DataRow dr = dt.NewRow();
                    dr["WONUM"] = wonum;
                    dr["BARCODE"] = barcode;
                    dr["DETAIL"] = detail;
                    dr["ACCOUNT"] = person;
                    dr["SPAREPARTID"] = person;
                    dt.Rows.Add(dr);
                }
                catch { }
            }
            return dt;
        }
        private DataSet GetSparepartid()
        {
            DataSet ds = null;
            string sql = "select distinct SPAREPARTID from RepairAsswet order by CREATEDT";
            try
            {
                ds = base.executeDataSet(sql);
            }
            catch { }
            return ds;
        }

        private DataTable CreateRepairAssetInfo()
        {
            DataTable dt = null;
            dt.Columns.Add("WONUM");
            dt.Columns.Add("BARCODE");
            dt.Columns.Add("DETAIL");
            dt.Columns.Add("ACCOUNT");
            dt.Columns.Add("SPAREPARTID");
            return dt;
        }*/

        /// <summary>
        /// 将数据上传数据库
        /// </summary>
        /// <param name="p_wonum">维修工单号</param>
        /// <param name="p_barcode">资产条码</param>
        /// <param name="itemnum">物料编号</param>
        /// <param name="quantity">数量</param>
        /// <param name="p_person">提交人</param>
        /// <returns></returns>
        public bool insertMaterial(string assignmentId, string p_wonum, string p_barcode, string p_person, string p_detail)
        {
            bool result = false;
            try
            {
                pdaWebReference.PdaService pda = base.getPdaWebService();
                result = pda.insertMaterial(assignmentId, p_wonum, p_barcode, p_person, p_detail);
                if (result)
                {
                    if (!deal(assignmentId, "4", DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"), ""))
                    {
                        result = false;
                    }
                }
            }
            catch (Exception e) { result=false; }
            return result;
        }


    }
}
